using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for clsxuly
/// </summary>
public class clsxuly : System.Web.UI.Page
{
    clsketnoi ketnoi = new clsketnoi();
    DataTable tbl = new DataTable();
    public clsxuly()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public DataTable LayBangDocGia()
    {
        try
        {
            string sql = "select * from DocGia";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    //---------------------------------------Tìm Kiếm Sách-----------------------------------

    public DataTable TimTheLoai(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and TuaSach.MaTL= N'" + tukhoa + "'";
            
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimTatCa(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and (TenTL Like N'%" + tukhoa + "%' or TenTG Like N'%" + tukhoa + "%' or DKCB Like'%" + tukhoa + "%' or TenTuaSach Like N'%" + tukhoa + "%')";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimTieuDeSach(string tukhoa)
    {
        try
        {

            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and TenTuaSach Like N'%" + tukhoa + "%'";
        
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimTacGia(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and TenTG Like N'%" + tukhoa + "%'";
            
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimNgonNgu(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and TenNN Like N'" + tukhoa + "%'";
            
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimNhaXB(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and Tennxb Like N'" + tukhoa + "%'";
            
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TimDKCB(string tukhoa)
    {
        try
        {
            string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and DKCB Like N'%" + tukhoa + "%'";
            
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    //public DataTable TimNangCao(string tensach, string tacgia, string nxb, string ngonngu)
    //{
    //    try
    //    {
    //        string sql = "select DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TenTuaSach=N'" + tensach + "' and TenTG=N'" + tacgia + "' and Tennxb=N'" + nxb + "' and TenNN=N'" + ngonngu + "'";
    //        tbl = ketnoi.Thuchientryvan(sql).Tables[0];
    //        return tbl;
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //}
    //-----------------------------------------------------------------------------------------------
    public DataTable LayTenSach()
    {
        string sql = "select * from TuaSach";
        tbl = ketnoi.Thuchientryvan(sql).Tables[0];
        return tbl;
    }
    public DataTable LayTheLoai()
    {
        try
        {
            string sql = "select * from TheLoai";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable MuonNhieuNhat()
    {
        try
        {
            string sql = "select CTMuonTra.DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia, count(CTMuonTra.DKCB)as SoLuong from TuaSach,Sach,TheLoai,TacGia,NgonNgu,NhaXuatBan,CTMuonTra where NhaXuatBan.Manxb=TuaSach.Manxb and NgonNgu.Mann=TuaSach.Mann and TheLoai.MaTL=TuaSach.MaTL and TacGia.MaTG=TuaSach.MaTG and TuaSach.MaTuaSach=Sach.MaTuaSach and CTMuonTra.DKCB=Sach.DKCB Group by CTMuonTra.DKCB,TenTuaSach,TenTL,TenTG,TrangThai,TenNN,Tennxb,Gia Having Count(CTMuonTra.DKCB) >= ALL(select count(CTMuonTra.DKCB)as SoLuong from TuaSach,Sach,CTMuonTra where TuaSach.MaTuaSach=Sach.MaTuaSach and CTMuonTra.DKCB=Sach.DKCB Group by CTMuonTra.DKCB)";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable LayBangTinTuc()
    {
        try
        {
            string sql = "select top 3 * from TinTuc order by MaTin desc";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
          throw ex;
        }
    }

    public DataTable TimChiTietTin(string ma)
    {
        try
        {
            string sql = "select * from TinTuc where MaTin='" + ma + "'";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable TinTuc()
    {
        try
        {
            string sql = "select * from TinTuc ";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable SachMoiNhat()
    {
        try
        {
            string sql = "select top 4 matuasach,tentuasach,hinhanh from tuasach order by matuasach DESC";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable TimGioiThieu()
    {
        try
        {
            string sql = "select * from TinTuc where MaTin='TT02'";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return tbl;


        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public int GioiThieu()
    {
        try
        {
            string sql = "select * from TinTuc where MaTin='TT02'";
            tbl = ketnoi.Thuchientryvan(sql).Tables[0];
            return 1;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
